IF OBJECT_ID('ssf.gbl_release_semaphore_connection') IS NOT NULL
	DROP PROCEDURE ssf.gbl_release_semaphore_connection
GO

CREATE PROCEDURE ssf.gbl_release_semaphore_connection
	@semaphore_name NVARCHAR(128)
	, @connections_to_release INT = 1
	, @locked_by_spid INT = NULL
	, @locked_by_user NVARCHAR(128) = NULL
	, @connections_released INT = 0 OUTPUT
AS
/*
<object_header>

<object_summary>
	<purpose><![CDATA[

	]]></purpose>
</object_summary>

</object_header>
*/

SET NOCOUNT ON

-- ============
/*<code_comment><![CDATA[declare dynamic sql variables]]></code_comment>*/
DECLARE	@proc_error INT
			, @sql_error INT
			, @error_message NVARCHAR(2048)
			, @error_line INT
			, @row_count BIGINT
			, @nsql NVARCHAR(MAX)
			, @crlf CHAR(2) = CHAR(13) + CHAR(10)
			, @quote CHAR(1) = CHAR(39)
			, @tab CHAR(1) = CHAR(9)
-- ============

-- ============
/*<code_comment><![CDATA[declare local variables]]></code_comment>*/
DECLARE	@variable_id INT = NULL
			, @current_modified_date DATETIME2(7) = NULL
			, @enabled_connections INT = NULL
			, @poll_interval_string VARCHAR(12) = NULL
-- ============

-- ============
/*<code_comment><![CDATA[initialize output parameters]]></code_comment>*/
SET @connections_released = 0
-- ============

-- ============
/*<code_comment><![CDATA[check parameters]]></code_comment>*/
IF NULLIF(@semaphore_name, N'') IS NULL
OR @connections_to_release IS NULL
BEGIN
	RAISERROR('A required parameter is blank or null.', 16, 1)
	RETURN 1
END

IF @locked_by_spid IS NULL
AND NULLIF(@locked_by_user, N'') IS NULL
BEGIN
	RAISERROR('Must specify at least 1 of @locked_by_spid and @locked_by_user.', 16, 1)
	RETURN 1
END

IF @connections_to_release < 1
BEGIN
	RAISERROR('Must release at least 1 connection.', 16, 1)
	RETURN 1
END
-- ============

-- ============
/*<code_comment><![CDATA[fail if semaphore does not exist]]></code_comment>*/
IF ssf.gbl_exists_variable(@semaphore_name) = 0
BEGIN
	RAISERROR('The semaphore <%s> does not exist.', 16, 1, @semaphore_name)
	RETURN 1
END
-- ============

-- ============
/*<code_comment><![CDATA[release connections]]></code_comment>*/
BEGIN TRY
	UPDATE TOP (@connections_to_release) ssf.gbl_semaphore_connection
	SET locked_by_spid = NULL
		, locked_by_user = NULL
		, modified_date = SYSDATETIME()
	WHERE semaphore_name = @semaphore_name
	AND locked_by_spid = ISNULL(@locked_by_spid, locked_by_spid)
	AND locked_by_user = ISNULL(NULLIF(@locked_by_user, N''), locked_by_user)
	
	SET @connections_released = @@ROWCOUNT
END TRY
BEGIN CATCH
	SELECT @sql_error = ERROR_NUMBER()
		, @error_message = ERROR_MESSAGE()
	
	RAISERROR(@error_message, 16, 1)
	RETURN @sql_error
END CATCH
-- ============

RETURN @@ERROR
GO
